﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Web;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace winfrom
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //定义变量来接受所有的错误的信息
        public static List<Project> errorList = new List<Project>();
        private void button1_Click_1(object sender, EventArgs e)
        {
            errorList = new List<Project>();
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = false;   //是否允许多选
            dialog.Title = "请选择要处理的文件";  //窗口title
            dialog.Filter = "ECEL文件(*.xlsx)|*.xlsx";   //可选择的文件类型

            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string path = dialog.FileName;  //获取文件路径
                String[] sheets = GetSheetName(path);
                if (sheets == null)
                {
                    return;
                }
                Dictionary<string, List<Project>> myDictionary = new Dictionary<string, List<Project>>();
                for (int i = 0; i < sheets.Length; i++)
                {
                    List<Project> projects = ExcelToTable(path, i, sheets[i], true);
                    myDictionary.Add(sheets[i], projects);
                }

                if (errorList.Count > 0)
                {
                    //表明存在错误的信息，直接返回，生成错误的excel数据
                    ExportDataToExcel();
                    return;
                }

                //比对两个文件的数据是否一致 读取放在本地的文件地址
                //1.先比对sheet页是否一致
                string filePath =  System.Environment.CurrentDirectory+"\\init.xlsx";
                String[] fileSheets = GetSheetName(filePath);
                var diffArr = fileSheets.Where(c => !sheets.Contains(c)).ToArray();
                if (diffArr.Length > 0)
                {
                    MessageBox.Show("导入文件比对成功,sheet页数量或名称对应不上。", "提示");
                    return;
                }
                Dictionary<string, List<Project>> fileDictionary = new Dictionary<string, List<Project>>();
                for (int i = 0; i < fileSheets.Length; i++)
                {
                    List<Project> projects = ExcelToTable(filePath, i, fileSheets[i], false);
                    fileDictionary.Add(fileSheets[i], projects);
                }

                //2.比对数据是否一致
                for (int i = 0; i < fileSheets.Length; i++)
                {
                    string sheet = fileSheets[i];
                    List<Project> oldList = fileDictionary[sheet];
                    List<Project> newList = myDictionary[sheet];
                    var exp2 = oldList.Where(a => !newList.Exists(t =>
                    a.ProjectNo.Contains(t.ProjectNo) && a.ProjectName.Contains(t.ProjectName) && a.ProjectFeature.Contains(t.ProjectFeature) && a.ProjectMeasure.Contains(t.ProjectMeasure)
                    )).ToList();
                    //这里表明文件完全存在
                    for (int j = 0; j < exp2.Count; j++)
                    {
                        Project pro = exp2[j];
                        pro.SortName = sheet;
                        pro.Msg = "数据缺失，或名称字段无法匹配。";
                        errorList.Add(pro);
                    }
                    var exp3 = newList.Where(a => !oldList.Exists(t =>
                 a.ProjectNo.Contains(t.ProjectNo) && a.ProjectName.Contains(t.ProjectName) && a.ProjectFeature.Contains(t.ProjectFeature) && a.ProjectMeasure.Contains(t.ProjectMeasure)
                 )).ToList();
                    for (int j = 0; j < exp3.Count; j++)
                    {
                        Project pro = exp3[j];
                        pro.SortName = sheet;
                        pro.Msg = "数据多余，或名称字段无法匹配。";
                        errorList.Add(pro);
                    }
                }
                if (errorList.Count > 0)
                {
                    //表明存在错误的信息，直接返回，生成错误的excel数据
                    ExportDataToExcel();
                    return;
                }
                MessageBox.Show("导入文件比对成功，无错误信息。", "提示");
            }
        }

        /**
         *  生成excel
         * 
         */
        public void ExportDataToExcel()
        {
            DateTime dt = DateTime.Now;
         
            string lu1 = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            string fileName = lu1 + "\\错误日志" + dt.ToFileTime().ToString() + ".xlsx";
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("错误信息日志");

            sheet.SetColumnWidth(1, 15 * 256);
            sheet.SetColumnWidth(2, 15 * 256);
            sheet.SetColumnWidth(3, 15 * 256);
            sheet.SetColumnWidth(4, 40 * 256);
            sheet.SetColumnWidth(5, 10 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 10 * 256);
            sheet.SetColumnWidth(8, 30 * 256);
            XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
            row1.Height = 30 * 20;
            //给标题的每一个单元格赋值
            row1.CreateCell(0).SetCellValue("序号");//0
            row1.CreateCell(1).SetCellValue("工程类型");//0
            row1.CreateCell(2).SetCellValue("项目编号");//0
            row1.CreateCell(3).SetCellValue("项目名称");//0
            row1.CreateCell(4).SetCellValue("项目特征");//0
            row1.CreateCell(5).SetCellValue("计量单位");//0
            row1.CreateCell(6).SetCellValue("工程量");//0
            row1.CreateCell(7).SetCellValue("综合单价");//0
            row1.CreateCell(8).SetCellValue("错误信息");//0
            
            XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //上
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//下
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//左
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//右
 
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            sheet.AutoSizeColumn(0);//自动适应所有列宽

            style.WrapText = true;
            
            row1.GetCell(0).CellStyle = style;
            row1.GetCell(1).CellStyle = style;
            row1.GetCell(2).CellStyle = style;
            row1.GetCell(3).CellStyle = style;
            row1.GetCell(4).CellStyle = style;
            row1.GetCell(5).CellStyle = style;
            row1.GetCell(6).CellStyle = style;
            row1.GetCell(7).CellStyle = style;
            row1.GetCell(8).CellStyle = style;

            for (int i = 0; i < errorList.Count; i++)
            {
               
                Project project = errorList[i];
                XSSFRow row = (XSSFRow)sheet.CreateRow((i + 1));
                row.Height = 100*20;
                row.CreateCell(0).SetCellValue(project.Num);//0
                row.CreateCell(1).SetCellValue(project.SortName);
                row.CreateCell(2).SetCellValue(project.ProjectNo);//0
                row.CreateCell(3).SetCellValue(project.ProjectName);//0
                row.CreateCell(4).SetCellValue(project.ProjectFeature);//0
                row.CreateCell(5).SetCellValue(project.ProjectMeasure);//0
                row.CreateCell(6).SetCellValue(project.ExcelProjectTotal);//0
                row.CreateCell(7).SetCellValue(project.ExcelPrice);//0
                row.CreateCell(8).SetCellValue(project.Msg);//0
                row.GetCell(0).CellStyle = style;
                row.GetCell(1).CellStyle = style;
                row.GetCell(2).CellStyle = style;
                row.GetCell(3).CellStyle = style;
                row.GetCell(4).CellStyle = style;
                row.GetCell(5).CellStyle = style;
                row.GetCell(6).CellStyle = style;
                row.GetCell(7).CellStyle = style;
                row.GetCell(8).CellStyle = style;
            }
            FileStream file = new FileStream(fileName, FileMode.Create, System.IO.FileAccess.Write);//产生文件
            workbook.Write(file);
            file.Close();
            //成功提示
            if (MessageBox.Show("错误日志生成成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
            {
                System.Diagnostics.Process.Start(fileName);
            }

        }


        /// <summary>
        /// 获取sheet表名
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string[] GetSheetName(string filePath)
        {
            int sheetNumber = 0;
            try

            {
                var   file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (filePath.IndexOf(".xlsx") > 0)
                {
                    var xssfworkbook = new XSSFWorkbook(file);
                    sheetNumber = xssfworkbook.NumberOfSheets;

                    string[] sheetNames = new string[sheetNumber];

                    for (int i = 0; i < sheetNumber; i++)
                    {
                        sheetNames[i] = xssfworkbook.GetSheetName(i);
                    }
                    return sheetNames;
                }
            }
            catch (Exception ex)
            {
                //错误处理...
                MessageBox.Show(ex.Message, "提示");
            }
            return null;
        }


       /***
        *  解析excel数据
        * 
        */
        public static List<Project> ExcelToTable(string file, int index, string sheetName,bool bools)
        {
            List<Project> list = new List<Project>();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(index);
                string msg = "";
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    msg = "";
                    Project project = new Project();
                    var fist = GetValueType(sheet.GetRow(i).GetCell(0));

                    if (fist !=null && fist.ToString() != string.Empty  && IsInt(fist.ToString()))
                    {
                        var Num = GetValueType(sheet.GetRow(i).GetCell(0));
                        var ProjectNo = GetValueType(sheet.GetRow(i).GetCell(1));
                        var ProjectName = GetValueType(sheet.GetRow(i).GetCell(3));
                        var ProjectFeature = GetValueType(sheet.GetRow(i).GetCell(5));
                        var ProjectMeasure = GetValueType(sheet.GetRow(i).GetCell(7));
                        var ExcelProjectTotal = GetValueType(sheet.GetRow(i).GetCell(8));
                        var ExcelPrice = GetValueType(sheet.GetRow(i).GetCell(10));
                       

                        if (ProjectNo == null || ProjectNo.ToString() == string.Empty)
                        {
                            msg = msg + "项目编号不能为空,";
                        }

                        if (ProjectName == null || ProjectName.ToString() == string.Empty)
                        {
                            msg = msg + "项目名称不能为空,";
                        }

                        if (ProjectFeature == null || ProjectFeature.ToString() == string.Empty)
                        {
                            msg = msg + "计量单位不能为空,";
                        }

                        if (ExcelProjectTotal == null || ExcelProjectTotal.ToString() == string.Empty)
                        {
                            msg = msg + "工程量不能为空，";
                        }

                        if(ExcelProjectTotal != null &&  ExcelProjectTotal.ToString() != string.Empty && !IsInt(ExcelProjectTotal.ToString()))
                        {
                            msg = msg + "工程量只能是数字,";
                        }
                        if (bools)
                        {
                            if (ExcelPrice == null || ExcelPrice.ToString() == string.Empty )
                            {
                                msg = msg + "单价不能为空,";
                            }
                            if (ExcelPrice != null &&  ExcelPrice.ToString() !=  string.Empty && !IsInt(ExcelPrice.ToString()))
                            {
                                msg = msg + "单价只能数字,";
                            }
                        }
                        project.Num = ( Num==null ? "": Num.ToString() );
                        project.ProjectNo = (ProjectNo == null ? "" : ProjectNo.ToString());
                        project.ProjectName = (ProjectName ==null ? "": ProjectName.ToString());
                        project.ProjectFeature = (ProjectFeature == null ? "" :  ProjectFeature.ToString());
                        project.ProjectMeasure = (ProjectMeasure == null ? "" : ProjectMeasure.ToString());
                        project.ExcelProjectTotal = (ExcelProjectTotal == null ? "" : ExcelProjectTotal.ToString());
                        project.ExcelPrice = (ExcelPrice == null ? "" : ExcelPrice.ToString());
                        project.Msg = msg;
                        project.SortName = sheetName;
                        if (msg != string.Empty)
                        {
                            errorList.Add(project);
                        }
                        else
                        {
                            list.Add(project);
                        }
                       
                    }
                }
            }
            return list;
        }

        /**
         * 判断是否是数字
         */
        public static bool IsInt(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?\d*[.]?\d*$");
        }
        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell">目标单元格</param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Formula:
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }

    /**
     * 导入的excel对象信息
     */
    public class Project
    {

        private string num;

        /**
        * 工程编号ID
        * */
        private string projectNo;

        /**
        * 项目名称
        * */
        private string projectName;

        /**
        * 项目特征
        * */
        private string projectFeature;

        /**
        * 计量单位
        * */

        private string projectMeasure;


        /**
         * 数量
         */
        private string excelProjectTotal;

        /**
    * 价格
    */
        private string excelPrice;

        /**
    * 总价
    */
        private string excelTotalPrice;
        /**
            * 错误信息
            */
        private string msg;

        private string sortName;

        public string Num { get => num; set => num = value; }
        public string ProjectNo { get => projectNo; set => projectNo = value; }
        public string ProjectName { get => projectName; set => projectName = value; }
        public string ProjectFeature { get => projectFeature; set => projectFeature = value; }
        public string ProjectMeasure { get => projectMeasure; set => projectMeasure = value; }
        public string ExcelProjectTotal { get => excelProjectTotal; set => excelProjectTotal = value; }
        public string ExcelPrice { get => excelPrice; set => excelPrice = value; }
        public string ExcelTotalPrice { get => excelTotalPrice; set => excelTotalPrice = value; }
        public string Msg { get => msg; set => msg = value; }
        public string SortName { get => sortName; set => sortName = value; }
    }
}
